﻿Imports System.Data.SqlClient

Public Class frmRenameAccounts

    Sub FillTree()
        Try
            Me.Cursor = Cursors.WaitCursor
            Dim cmd As New SqlCommand("Select Acc1 From Acc Where Acc1 Is Not Null and Acc1<>N'' and AccNo2=0", cnn)
            Dim Reader, Reader1, Reader2, Reader3 As SqlDataReader
            Dim i, i1, i2 As Integer

            Me.TreeAcc.Nodes.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.TreeAcc.Nodes.Add(Reader.Item(0))
                Dim cmd1 As New SqlCommand("Select Acc2 From Acc Where Acc1=N'" & Reader.Item(0) & _
                                           "' and Acc2 Is Not Null and Acc2<>N'' and AccNo3=0", cnn1)

                cnn1.Open()
                Reader1 = cmd1.ExecuteReader
                While Reader1.Read
                    Me.TreeAcc.Nodes(i).Nodes.Add(Reader1.Item(0))
                    Dim cmd2 As New SqlCommand("Select Acc3 From Acc Where Acc1=N'" & Reader.Item(0) & "' and " & _
                                               "Acc2=N'" & Reader1.Item(0) & "' and Acc3 Is Not Null and Acc3<>N'' and AccNo4=0", cnn2)

                    cnn2.Open()
                    Reader2 = cmd2.ExecuteReader
                    While Reader2.Read
                        Me.TreeAcc.Nodes(i).Nodes(i1).Nodes.Add(Reader2.Item(0))
                        Dim cmd3 As New SqlCommand("Select Acc4 From Acc Where Acc1=N'" & Reader.Item(0) & "' and " & _
                                                  "Acc2=N'" & Reader1.Item(0) & "' and Acc3=N'" & Reader2.Item(0) & _
                                                  "' and Acc4 Is Not Null and Acc4<>N'' and AccNo4<>0", cnn3)
                        cnn3.Open()
                        Reader3 = cmd3.ExecuteReader
                        While Reader3.Read
                            Me.TreeAcc.Nodes(i).Nodes(i1).Nodes(i2).Nodes.Add(Reader3.Item(0))
                        End While
                        cnn3.Close()
                        i2 += 1
                    End While
                    cnn2.Close()
                    i2 = 0
                    i1 += 1
                End While

                cnn1.Close()
                i2 = 0
                i1 = 0
                i += 1
            End While
            cnn.Close()
            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            If cnn1.State = ConnectionState.Open Then
                cnn1.Close()
            End If
            If cnn2.State = ConnectionState.Open Then
                cnn2.Close()
            End If
            If cnn3.State = ConnectionState.Open Then
                cnn3.Close()
            End If
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmRenameAccounts_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized

        FillTree()
    End Sub

    Private Sub RenameToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles RenameToolStripMenuItem.Click
        If Me.TreeAcc.SelectedNode Is Nothing Then
            Exit Sub

        ElseIf Me.TreeAcc.SelectedNode.Level > -1 Then
            If CheckAccount(Me.TreeAcc.SelectedNode.Text) = True Then
                MsgBox("You are not allowed to change this account.")
            Else
                Try
                    Me.Cursor = Cursors.WaitCursor

                    Dim OldName, NewName, Column As String

                    OldName = Me.TreeAcc.SelectedNode.Text
                    NewName = GetNewName(OldName)

                    If Me.TreeAcc.SelectedNode.Level = 0 Then
                        Column = "Acc1"

                    ElseIf Me.TreeAcc.SelectedNode.Level = 1 Then
                        Column = "Acc2"

                    ElseIf Me.TreeAcc.SelectedNode.Level = 2 Then
                        Column = "Acc3"

                    ElseIf Me.TreeAcc.SelectedNode.Level = 3 Then
                        Column = "Acc4"

                    End If

                    Dim cmd As New SqlCommand
                    Dim Trans As SqlTransaction

                    cnn.Open()
                    Trans = cnn.BeginTransaction
                    cmd.Connection = cnn
                    cmd.Transaction = Trans

                    'Update table Acc
                    cmd.Parameters.Clear()
                    cmd.CommandText = "Update Acc Set " & Column & "=@NewName Where " & Column & "=@OldName"
                    cmd.Parameters.AddWithValue("@NewName", NewName)
                    cmd.Parameters.AddWithValue("@OldName", OldName)
                    cmd.ExecuteNonQuery()

                    'Update Transactions
                    cmd.Parameters.Clear()
                    cmd.CommandText = "Update Transactions Set " & Column & "=@NewName Where " & Column & "=@OldName"
                    cmd.Parameters.AddWithValue("@NewName", NewName)
                    cmd.Parameters.AddWithValue("@OldName", OldName)
                    cmd.ExecuteNonQuery()

                    'Update TempTransactions
                    cmd.Parameters.Clear()
                    cmd.CommandText = "Update TempTransactions Set " & Column & "=@NewName Where " & Column & "=@OldName"
                    cmd.Parameters.AddWithValue("@NewName", NewName)
                    cmd.Parameters.AddWithValue("@OldName", OldName)
                    cmd.ExecuteNonQuery()

                    'Update Dictionary
                    cmd.Parameters.Clear()
                    cmd.CommandText = "Update AccDictionary Set AccNameEng=@NewName Where AccNameEng=@OldName"
                    cmd.Parameters.AddWithValue("@NewName", NewName)
                    cmd.Parameters.AddWithValue("@OldName", OldName)
                    cmd.ExecuteNonQuery()

                    Trans.Commit()
                    cnn.Close()

                    MsgBox("Renamed Successfully")
                    FillTree()

                    Me.Cursor = Cursors.Default
                Catch ex As Exception
                    Me.Cursor = Cursors.Default
                    If cnn.State = ConnectionState.Open Then
                        cnn.Close()
                    End If
                    MsgBox(ex.ToString)
                End Try
            End If
        End If
    End Sub

    Function GetNewName(ByVal OldName As String) As String
        Try
            Dim NewName As String
            NewName = InputBox("Please enter the new account name", "Rename", OldName)
            
            Return NewName
        Catch ex As Exception

        End Try
    End Function

    Function CheckAccount(ByVal Account As String) As Boolean
        Try
            Dim List As New ArrayList
            List.Add("Assets")
            List.Add("Current Assets")
            List.Add("Accounts Receivable (Commission from Insurance Companies)")
            List.Add("Tawuniya")
            List.Add("Revenues")
            List.Add("Commission")
            List.Add("Commission Earned from Insurance")
            List.Add("Expenses")
            List.Add("Commission Expenses")
            List.Add("Brokers Commission")
            List.Add("Liabilities")
            List.Add("Current Liabilities")
            List.Add("Accounts Payable (Sub-Brokers)")

            If List.IndexOf(Account) > -1 Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Function
End Class